﻿using Dapper;
using Entity;
using IRealize;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Realize
{
    public class ER : IER
    {
        public async Task<int> Add(string HumanName, string HumanEmail, string HumanTelephone, string HumanHomephone, string HumanMobilephone, string HumanAddress, string HumanPostcode, string HumanBirthplace, string HumanBirthday, string HumanIdcard, int HumanAge, string HumanCollege, string DemandSalaryStandard, string RegisterTime, string HumanHistoryRecords, string Remark, string HumanMajorKindId, string HumanMajorKindName, string HumanMajorId, string HunmaMajorName, string value1, string value2, string value3, string value4, string value5, string value6, string value7, int value8, string value9, string value10, string value11, string image,string sqlserver)
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $@"insert into engage_resume(human_name,human_email,human_telephone,human_homephone,human_mobilephone,human_address,
                            human_postcode,human_birthplace,human_birthday,human_idcard,human_age,human_college,demand_salary_standard,
                            regist_time,human_history_records,remark,human_major_kind_id,human_major_kind_name,human_major_id,human_major_name,
                            engage_type,human_sex,human_nationality,human_race,human_religion,human_party,human_hobby,human_educated_years,
                            human_educated_degree,human_educated_major,human_specility,human_picture) 
                            values('{HumanName}','{HumanEmail}','{HumanTelephone}','{HumanHomephone}','{HumanMobilephone}','{HumanAddress}',
                                    '{HumanPostcode}','{HumanBirthplace}','{HumanBirthday}','{HumanIdcard}',{HumanAge},'{HumanCollege}',
                                    '{DemandSalaryStandard}','{RegisterTime}','{HumanHistoryRecords}','{Remark}','{HumanMajorKindId}',
                                    '{HumanMajorKindName}','{HumanMajorId}','{HunmaMajorName}','{value1}','{value2}','{value3}','{value4}',
                                    '{value5}','{value6}','{value7}','{value8}','{value9}','{value10}','{value11}','{image}')";

                return await con.ExecuteAsync(sql);
            }
        }

        public async Task<int> Del(string id, string sqlserver)
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $"delete from engage_resume where res_id = {id}";
                return await con.ExecuteAsync(sql);
            }
        }

        public async Task<Engage_Resume> GetCandidateInfo(string resumeId, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                string sql = "SELECT * FROM Engage_Resume WHERE id = @ResumeId";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@ResumeId", resumeId);

                await conn.OpenAsync();
                using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        return new Engage_Resume
                        {
                            ein_id = Convert.ToInt32(reader["id"]),
                            human_email = reader["human_email"].ToString(),
                            // 其他属性...
                        };
                    }
                }
            }

            return null;
        }
        

        public async Task<int> Lysp(string id, string zhi, string sqlserver)//录用审批
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $"update engage_resume set pass_passComment = '{zhi}' where res_id = {id}";
                return await con.ExecuteAsync(sql);
            }
        }

        public async Task<int> LySq(string id, string zhi, string sqlserver)//录用申请
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $"update engage_resume set pass_checkComment = '{zhi}' where res_id = {id}";
                return await con.ExecuteAsync(sql);
            }
        }

        public async Task<List<Engage_Resume>> Select(string sqlserver)//筛选通过之后的查询
        {
            Task<List<Engage_Resume>> task = Task<List<Engage_Resume>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = "select * from engage_resume where interview_status=1";
                    return con.Query<Engage_Resume>(sql).ToList();
                }

            });
            return await task;
        }

        public async Task<List<Engage_Resume>> Select2(string sqlserver)
        {
            Task<List<Engage_Resume>> task = Task<List<Engage_Resume>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = "select * from engage_resume where interview_status=0";
                    return con.Query<Engage_Resume>(sql).ToList();
                }

            });
            return await task;
        }

        public async Task<Engage_Resume> SelectById(string id, string sqlserver)
        {
            Task<Engage_Resume?> task = Task<Engage_Resume>.Run(() =>
            {
                using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
                {
                    string sql = $@"select * from engage_resume where res_id={id}";
                    return sqlConnection.Query<Engage_Resume>(sql).FirstOrDefault();
                }
            });
            return await task;
        }

        public async Task<List<Engage_Resume>> selectTj(string id1, string id2, string Tj, string ksTime, string jsTime,string sqlserver)
        {
            Task<List<Engage_Resume>> task = Task<List<Engage_Resume>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = $@"SELECT * FROM dbo.engage_resume WHERE interview_status = 0 ";

                    if(id1 !=null && id2 != null)//是否选择职位
                    {
                        sql += $@" And human_major_kind_id = {id1}  AND human_major_id = {id2} ";
                    }

                    if (Tj != null)//输入框是否有输入
                    {
                        sql += $@" AND (human_mobilephone LIKE '%{Tj}%' OR human_idcard LIKE '%{Tj}%' OR human_name LIKE '%{Tj}%') ";
                    }

                    if (ksTime != null && jsTime != null)//登记时间
                    {
                        sql += @$" AND regist_time > '{ksTime}' And regist_time < '{jsTime}' ";
                    }

                    return con.Query<Engage_Resume>(sql).ToList();
                }

            });
            return await task;
        }

        public async Task<List<Engage_Resume>> selectYxJj(string id1, string id2, string Tj, string ksTime, string jsTime, string sqlserver)
        {
            Task<List<Engage_Resume>> task = Task<List<Engage_Resume>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = $@"SELECT * FROM dbo.engage_resume WHERE interview_status = 1 ";

                    if (id1 != null && id2 != null)//是否选择职位
                    {
                        sql += $@" And human_major_kind_id = {id1}  AND human_major_id = {id2} ";
                    }

                    if (Tj != null)//输入框是否有输入
                    {
                        sql += $@" AND (human_mobilephone LIKE '%{Tj}%' OR human_idcard LIKE '%{Tj}%' OR human_name LIKE '%{Tj}%') ";
                    }

                    if (ksTime != null && jsTime != null)//登记时间
                    {
                        sql += @$" AND regist_time > '{ksTime}' And regist_time < '{jsTime}' ";
                    }
                    return con.Query<Engage_Resume>(sql).ToList();
                }
            });
            return await task;
        }

        public async Task<List<Engage_Resume>> Select_LY(string sqlserver)
        {
            Task<List<Engage_Resume>> task = Task<List<Engage_Resume>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = $@"SELECT * 
FROM dbo.engage_resume er 
INNER JOIN dbo.engage_interview ei 
    ON er.res_id = ei.resume_id 
WHERE 
    er.interview_status = 3 
    AND er.pass_checkComment IS NULL;";
                    return con.Query<Engage_Resume>(sql).ToList();
                }
            });
            return await task;
        }

        public async Task<List<Engage_Resume>> Select_LyCx(string sqlserver)//录用查询
        {
            Task<List<Engage_Resume>> task = Task<List<Engage_Resume>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = $@"select * from dbo.engage_resume er 
                                    inner join dbo.engage_interview ei
                                    on er.res_id = ei.resume_id where pass_passComment = '通过'";
                    return con.Query<Engage_Resume>(sql).ToList();
                }
            });
            return await task;
        }

        public async Task<List<Engage_Resume>> Select_LySp(string sqlserver)
        {
            Task<List<Engage_Resume>> task = Task<List<Engage_Resume>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = $@"select * from dbo.engage_resume er 
                                    inner join dbo.engage_interview ei
                                    on er.res_id = ei.resume_id where pass_checkComment = '通过'";
                    return con.Query<Engage_Resume>(sql).ToList();
                }
            });
            return await task;
        }

        public async Task<int> Tgms(string id, string jlid,string zt, string sqlserver)//面试筛选建议录用
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $@"Update engage_interview set check_status='{zt}' where ein_id = {id} ";
                int result = await con.ExecuteAsync(sql);

                if (zt == "1"&&result>0)
                { 
                    sql = $@"Update engage_resume set interview_status = 3 where res_id = {jlid}";
                    result = await con.ExecuteAsync(sql);
                    sql = $@"Delete from engage_interview where interview_status != 3 and resume_id = {jlid}";
                    int result1 = await con.ExecuteAsync(sql);
                }

                if (zt == "-1" && result > 0)
                {
                    sql = $@"delete from engage_interview where resume_id = {id}";
                    result = await con.ExecuteAsync(sql);
                    sql = $@"delete from engage_resume where res_id = {id}";
                    result = await con.ExecuteAsync(sql);
                }

                if (zt=="0"&&result>0)
                {
                    sql = $@"Update engage_resume set interview_status = 1 where res_id = {jlid}";
                    result = await con.ExecuteAsync(sql);
                    sql = $@"delete from engage_interview where resume_id = {jlid}";
                    int result1 = await con.ExecuteAsync(sql);
                }

                return result;
            }
        }

        public async Task<int> Upd(string id, string tjr, string tjyj,string tjTime,string sqlserver)
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            { 
                string sql = $@"update engage_resume set Referrers = '{tjr}',recomandation='{tjyj}',Recommended_time='{tjTime}',interview_status=1 where res_id='{id}'";
                return await con.ExecuteAsync(sql);
            }
        }



    }
}
